package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.SMApplicationLog;

/**
 * A class used for managing SM_APPLICATION_LOG
 */
public class SMApplicationLogDAO {
	
	/** a database of this SM_APPLICATION_LOG */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public SMApplicationLogDAO(Database db) {
        this.db = db;
    }

    /**
     * a method for searching all SM_APPLICATION_LOG in database
     * @return all SM_APPLICATION_LOG
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM sm_application_log";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    
    /**
     * a method for adding a SM_APPLICATION_LOG into database
     * @param SM_APPLICATION_LOG to add
     */
    public void addLog(SMApplicationLog poSMApplicationLog) {
        String vaSql = "INSERT INTO sm_application_log" + " (log_id, program_code, event_date, event_time, "
        		+ "user_id, class_name, log_description, url_address, agent_browser)" 
        		+ " VALUES(sm_application_log_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            db.insert(vaSql,
            		poSMApplicationLog.getVaProgramCode(),
            		poSMApplicationLog.getVaEventDate(),
            		poSMApplicationLog.getVaEventTime(),
            		poSMApplicationLog.getVnUserId(),
            		poSMApplicationLog.getVaClassName(),
            		poSMApplicationLog.getVaLogDescription(),
            		poSMApplicationLog.getVaUrlAddress(),
            		poSMApplicationLog.getVaAgentBrowser());
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }

	public List<Map<String, Object>> findAllApplicationLogJoinUserLogin(int pnStart,
			int pnAmount) {
		int start = pnStart;
    	int end = start+pnAmount-1;
    	if(end==-1){
    		String vaSql = "" +
            		"SELECT * " +
            		"FROM SM_APPLICATION_LOG s " +
            		"LEFT OUTER JOIN USER_LOGIN u " +
            		"ON s.USER_ID = u.USER_ID " +
            		"ORDER BY s.EVENT_DATE ASC,s.EVENT_TIME ASC ";
            List<Map<String, Object>> result = db.queryList(vaSql);
            if (result != null) {
                return result;
            } else {
                return null;
            }
    	}
    	    	
    	
        String vaSql = "" +
        		"SELECT * " +
        		"FROM " +
        		"( " +
        		"SELECT ROWNUM r,sm.* " +
        		"FROM " +
        		"( " +
        		"SELECT * " +
        		"FROM SM_APPLICATION_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
        		"ORDER BY s.EVENT_DATE ASC,s.EVENT_TIME ASC " +
        		") sm " +
        		"WHERE ROWNUM <="+end+" "+
        		") " +
        		"WHERE r>="+start+" ";
        
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
	}

	public Object countApplicationLogJoinUserLogin() {
		String vaSql = "SELECT COUNT(*) FROM ( " +
    			"SELECT * " +
        		"FROM SM_APPLICATION_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
    			") ";
    	
        Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
	}

    public List<Map<String,Object>> searchApplicationLogJoinUserLogin(String paStartDate,String paEndDate,String paUserName,int pnStart,int pnAmount){
    	
    	int vnStart = pnStart+1;
    	int vnEnd = vnStart+pnAmount-1;
    	
    	
    	if(vnEnd==-1){
    		String vaSql = "" +
            		"SELECT * " +
            		"FROM SM_APPLICATION_LOG s " +
            		"LEFT OUTER JOIN USER_LOGIN u " +
            		"ON s.USER_ID = u.USER_ID " +
            		"WHERE 1=1 ";
        	if(!paStartDate.equals("")){
        		vaSql+="AND s.EVENT_DATE>= "+paStartDate+" ";
        	}
        	if(!paEndDate.equals("")){
        		vaSql+="AND s.EVENT_DATE<= "+paEndDate+" ";
        	}
        	if(!paUserName.equals("")){
        		vaSql+="AND u.USER_NAME LIKE  (LOWER('%"+paUserName+"%')) ";  
        	}
        	
        	vaSql+="ORDER BY s.EVENT_DATE ASC , s.EVENT_TIME ASC ";
        	
            List<Map<String, Object>> result = db.queryList(vaSql);
            if (result != null) {
                return result;
            } else {
                return null;
            }
    	}
    	
 
    	String vaSql = "" +
        		"SELECT * " +
        		"FROM " +
        		"( " +
        		"SELECT ROWNUM r,sm.* " +
        		"FROM " +
        		"( " +
        		"SELECT * " +
        		"FROM SM_APPLICATION_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
        		"WHERE 1=1 ";
    	if(!paStartDate.equals("")){
    		vaSql+="AND s.EVENT_DATE>="+paStartDate+" ";
    	}
    	if(!paEndDate.equals("")){
    		vaSql+="AND s.EVENT_DATE<="+paEndDate+" ";
    	}
    	if(!paUserName.equals("")){
    		vaSql+="AND u.USER_NAME LIKE  (LOWER('%"+paUserName+"%')) ";  
    	}
    	
    	vaSql+="ORDER BY s.EVENT_DATE ASC , s.EVENT_TIME ASC "+
        		") sm " +
        		"WHERE ROWNUM <="+vnEnd+" "+
        		") " +
        		"WHERE r>="+vnStart+" ";
    	
    	List<Map<String, Object>> result = db.queryList(vaSql);
    	  if (result != null) {
              return result;
          } else {
              return null;
          }
    }
    
 public int countSearchApplicationLogJoinUserLogin(String paStartDate,String paEndDate,String paUserName){
    	
		String vaSql = " " +
				"SELECT COUNT(*) FROM ( " +
        		"SELECT * " +
        		"FROM SM_APPLICATION_LOG s " +
        		"LEFT OUTER JOIN USER_LOGIN u " +
        		"ON s.USER_ID = u.USER_ID " +
        		"WHERE 1=1 ";
    	if(!paStartDate.equals("")){
    		vaSql+="AND s.EVENT_DATE>="+paStartDate+" ";
    	}
    	if(!paEndDate.equals("")){
    		vaSql+="AND s.EVENT_DATE<="+paEndDate+" ";
    	}
    	if(!paUserName.equals("")){
    		vaSql+="AND u.USER_NAME LIKE  (LOWER('%"+paUserName+"%')) ";  
    	}
    	
    	vaSql+= ") ";
    	Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    	
    }

}
